Exercise 1: Simple Atomic import

  1. Open MS Excel and the Parties module.
  2. Locate the back-end name for the Party Type field:
    1. Click What's this? in the top right corner of the Parties module and then click anywhere in the Party Type field on the Person tab

      -OR-

      With the cursor in the Party Type field, use the F1 keyboard shortcut.

      The Field Help window will display.

    2. If the More link displays, click it to expand the Field Help window fully.

      In the Field Information section, note the Column and the Kind:

      • The Column name for the Party Type field is NamPartyType.
      • The Kind is Atomic.
  3. Enter the Column name at the top of the first column in Excel (cell A1). You can copy (CTRL+c) the Column name in the Field Help window and paste it (CTRL+v) into Excel.

    Note: The first line in every TSV / CSV file mustcontain the field names as column headings. This row is also known as the header row.

  4. Locate the back-end name for the following fields and add them to the first row of the spreadsheet:
    • First: (Person Details)
    • Middle: (Person Details)
    • Last: (Person Details)
    • Notes (Notes)

    If your spreadsheet looks like this, you're doing well:

    ex_excel_atomic.gif

    After the first row, each row contains the data for a single record. The values are simply entered in the appropriate column.

  5. Complete row 2 in the spreadsheet with details for John Smith (use your own login name, e.g. Train1, where appropriate):

    ex_excel_atomic2.gif

  6. Enter your details in row 3 (columns A to D).
  7. Adding a note in multiple paragraphs is not as simple as entering text and hitting the Enter key as hitting Enter in an Excel cell will move the cursor to the next row in the spreadsheet rather than adding a new line within the current cell.

    To add a note in multiple paragraphs:

    1. Enter the first paragraph, e.g. [Your name] has been employed at the National Museum for 3 years.
    2. Press ALT+Enter to add a new line.
    3. Enter the next paragraph, e.g. A very useful contact.

    When this data is imported into EMu, this note will display as:

    mod_parties_note_tab_train.gif

    Your spreadsheet should appear similar to:

    ex_excel_atomic3.gif

    We're ready to save the Import data file as a Comma Separated Value (.csv) file.

  8. Click Save in the Excel Tool bar.

    The Save As dialog displays.

  9. Select CSV (Comma delimited) (*.csv) from the Save as type drop list.
  10. Save the file to your desktop:
    1. Name the file exercise1_Train1.csv, where Train1 is your login name.
    2. Click Save.

      The following message will display:

    msg_excel_unsupported_workbook.gif

    When we create a document in Excel it is a Workbook by default, but the CSV format only supports a single work sheet. This message will display every time you attempt to save a Workbook (multiple work sheets).

    1. Click OK.

      The following message will display:

    msg_excel_unsupported_features.gif

    This message will display the first time you attempt to save a new .csv file or after making changes to a .csv file.

    1. Click Yes.
  11. Close Excel.

    That's it. Now we can import the data.

    Note: Once the Import data file has been completed, the following steps (importing the data into EMu) are identical for each file type (.csv, .txt, .xml).

  12. In the Parties module, select Tools>Import from the Menu bar.

    Note: If this menu option is grayed out, you do not have the daImport permission.

    The Select File To Import dialog displays.

  13. Locate and double-click exercise1_Train1.csv, where Train1 is your login name.

    The Import Wizard dialog displays:

    Import Type screen

    The default option is Typical. You would accept this option to import data using the following import settings:

    • Import data, performing format and data validation.
    • If the data file references other records (Attachments):
      • A search is performed on all existing records in the EMu database.
      • If no match is found, a new record is added for the referenced record.
      • If one record is found, an attachment is made to that record.
      • If more than one match is found, the matching records are displayed so that the correct record can be selected manually.
    • All records are imported.
    • A minimal log is generated containing setup options, results and a list of errors if any occur.

    You'd select the Custom option to modify any of these import settings.

    As you can see from this list of default import settings, the Import data file is validated during a Typical import. If the format of the data file is invalid (more often than not due to an incorrectly entered field name in the first row in the case of a CSV/TSV file) or the data is invalid (a date field doesn't contain a date for instance), the import will fail for affected records (in the case of invalid data) or the entire data file (if the column name is invalid).

    To avoid any such complications it is possible to validate the data file before commencing an import. For this exercise, we'll first validate the Import data file to ensure that it has been created correctly (in future you may only want to perform this test when working with more complex Import data files).

  14. On the Import Type screen of the Import Wizard click the Custom radio button and click Next to continue.

    The Validate Data screen displays:

    db_validate_data_train.gif

    Three options are available:

    • Validate format

      Select this option only to check that the format of the Import data file is valid (e.g. that the correct column names have been used; checks that the format of a CSV file is valid and that XML is not malformed). The records in the data file will not be imported into EMu. Instead a report is generated.

      You would choose this option to ensure that the format of the Import data file will not generate any errors when you do import it.

    • Validate data

      As for Validate formatbut also checks that the data in the data file is valid (e.g. that date fields contain dates). The records in the data file will not be imported into EMu. Instead a report is generated.

      You would choose this option to ensure that the data and format of the Import data file will not generate any errors when you do import it.

    • Import data

      This is the default option. When selected the data and format of the data file will be validated and the records will be imported. If there is invalid data or format in the file:

      • An error will be generated and recorded in the log file.
      • An additional file is auto-generated containing any erroneous record. This file is in the same format as the original Import data file (.csv or .xml for instance) and, once the errors have been corrected, can be used to complete the import of the remaining record(s).
  15. Click the Validate data radio button and click Next to continue.

    The Records screen displays with options to specify:

    • The record in the Import data file from which to commence the import (the Starting record). By default this is the first record.
    • The number of records in the data file to import. By default this is all records.
  16. Click Next to continue.

    The Report screen displays with options to specify:

    • How much detail to include in the auto-generated Import Report.

      By default only import settings, import results and errors are reported.

    • Where to save the auto-generated Import Report and the error file (which is generated if there are errors).

      By default the files are saved in the same location as the data file used for the import.

  17. Take a look at the options available for future reference and click Next to continue.

    The Settings screen displays a list of the settings that will be used to import data from the selected file. Typically you would look over these settings and if necessary click Back to return to a prior step to change the options.

  18. Click Next to continue.

    The Importing screen displays and the validation is processed.

    If all goes well, your Importing screen will appear as below with a 0 in the Invalid column:

    db_importing_train2.gif

    If all does not go well, the error message that displays will probably be similar to:

    msg_import_fail_train.gif

    In this case, you'd:

    1. Click OK to continue.
    2. Click Finished.
    3. Click Close to exit the Import Wizard and display the Import Report log:

    notepad_import_train3.gif

    1. Note the error - in this case a bad column name (in this example, NotNootes has been entered instead of NotNotes) - and correct the error in the data file.
    2. Run the validation again until there are no errors (from Step 12).
  19. All should have gone well at Step 18, so click Finished.

    The Import Complete screen displays with a summary of the number of records processed:

    db_finished_train.gif

    By default the View Import Report checkbox is checked.

  20. Click Close to exit the Import Wizard and display the Import Report log:

    notepad_import_train2.gif

    As we have a valid Import data file, we can proceed with the import.

  21. In the Parties module, select Tools>Import from the Menu bar.

    The Select File To Import dialog displays.

  22. Locate and double-click exercise1_Train1.csv, where Train1 is your login name.

    The Import Wizard dialog displays:

    Import Type screen

  23. For this exercise, accept the Typicaloption and click Next to continue.

    The Import Identifier screen displays:

    db_import_identifier_train.gif

    When new records are imported a unique System identifier is automatically generated and saved in any newly created records.

    Note: A System identifier is not added to an existing record that is updated during the import: when the Import Tool is used, new records get a System identifier, existing records do not.

    The identifier is constructed from the username, and the date and time (24 hour clock) the import commenced. The format of the System identifier is username-yymmdd-hhmm.

    Note: The System identifier can be used to locate all records imported in a particular batch.

    You have the option to add your own identifier in the Import Identifier field.

    Note: Although the System identifier is a unique value, the Import Identifier can be re-used (to identify all records imported over time by the same user, for instance). For later reference, both identifiers are recorded in the log file generated with each import (typically saved in the same location as the Import data file used).
    An Import Identifier is not added to an existing record that is updated during the import: when the Import Wizard is used, new records get an Import Identifier, existing records do not.

  24. In the Import Identifier field, enter Train1, where Train1 is your login name:

    db_import_identifier_train2.gif

    Any time it is necessary to locate all records that you imported into the Parties module, simply search for your login name (or whatever identifier you choose to use) in the Import Identifier field on a module's Admin tab.

    Note: During this training course, always enter your login name in the Import Identifier field when performing an import.

  25. Click Next to continue.

    The Settings screen displays a list of the settings that will be used to import data from the selected file:

    db_settings_train.gif

  26. Scroll through the list of settings to see what a Typical Import involves.

    Many of these settings can be modified when you select Custom at the Import Type screen (Steps 14 and 15 above).

  27. Click Next to proceed with the import.

    The Importing screen displays and the import is processed. As there are no validation errors with this Import data file and it does not involve attachments, the import completes successfully without requesting any user intervention and details about the import are shown (how many records were created or updated, how many attachments were made and the number of errors):

    db_importing_train.gif

  28. Click Finished.

    The Import Complete screen displays with a summary of the number of records processed:

    db_finished_train.gif

    By default the View Import Report checkbox is checked.

  29. Click Close to exit the Import Wizard and display the Import Report log:

    notepad_import_train.gif

    All records without errors are imported into EMu. Any records with errors are identified in the Import Report and are output to an error file (which is in the same format as the original Import data file). Both files are typically saved to the same location as the original Import data file (the Desktop in this exercise).

    The imported records display in the module window:

    mod_parties_imported.gif

    Note: Any records listed or displaying prior to the import will be discarded: only the imported records will display.

    And that, in its simplest form, is how a CSV/TSV Import data file is created and imported.

Note: If your version of EMu is older than version 5.0, replace encoding="UTF-8" with encoding="iso-8859-1" in the following exercises.

For future reference, the XML for this example is:

<?xml version="1.0" encoding="UTF-8"?>
<table>
	<!-- First record-->
	<tuple>
		<atom name="NamPartyType">Person</atom>
		<atom name="NamFirst">John</atom>
		<atom name="NamMiddle">Train20</atom>
		<atom name="NamLast">Smith</atom>
		<atom name="NotNotes">Former curator at the National Museum.</atom>
	</tuple>
	<!--Second record-->
	<tuple>
		<atom name="NamPartyType">Person</atom>
		<atom name="NamFirst">Gerard</atom>
		<atom name="NamMiddle">I.</atom>
		<atom name="NamLast">Wood</atom>
		<atom name="NotNotes">Gerard has been employed at the National Museum for 3 years.
A very useful contact.</atom>
	</tuple>
</table>

Note:

  1. Except for the XML declaration (called a processing instruction) at the very top of the code (<?xml version="1.0" encoding="UTF-8"?>), XML tags occur as pairs where each pair must have an opening and closing tag, e.g. <table> and </table>.
  2. You could use an XML editor to create this file or even a basic text editor (simply save the file with a .xml extension).

Commented code:

<?xml version="1.0" encoding="UTF-8"?>
<!--This is the XML declaration and should appear at the beginning of any XML document; it identifies the document as XML and specifies the release of XML that it conforms to. The encoding defines the character set of the raw data; UTF-8 specifies the Unicode character set used by EMu5.0 onwards (if your version is older than 5.0, specify encoding="ISO-8859-1" instead). If this declaration is not present, then a default setting is used. The default setting assumes the input character set is UTF-8, which in some cases may be incorrect. If the data is in ISO-8859-1 encoding for instance, then the processing instruction MUST be specified, otherwise the parser will complain about bad UTF-8 characters.-->
<--! The opening and closing table tags must surround the data to be imported-->
<table>
	<!--First record-->
	<!--Each tuple represents a single record. A tuple can include atoms, tuples and tables-->
	<tuple>
		<!--Each atom represents a single value. The format for an atom is: <atom name="colname">value</atom> -->
		<atom name="NamPartyType">Person</atom>
		<atom name="NamFirst">John</atom>
		<atom name="NamMiddle">Train20</atom>
		<atom name="NamLast">Smith</atom>
		<atom name="NotNotes">Former curator at the National Museum.</atom>
	</tuple>
	<!--Second record-->
	<tuple>
		<!--When specifying atoms, only fields that contain values are specified. In the previous record (tuple) five values were specified; if this one didn't have a middle name for example, you wouldn't include an atom for NamMiddle-->
		<atom name="NamPartyType">Person</atom>
		<atom name="NamFirst">Gerard</atom>
		<atom name="NamMiddle">I.</atom>
		<atom name="NamLast">Wood</atom>
		<!--Text and paragraph formatting entered between the atom tags is retained when the XML is imported into EMu. In this example, twolines of text are specified in the XML, and two lines will be imported into the Notes field-->
		<atom name="NotNotes">Gerard has been employed at the National Museum for 3 years.
A very useful contact.</atom>
	</tuple>
<!--Don't forget to close off all of the tags-->
</table>